package com.ruoyi.project.module.ny;

import com.ruoyi.common.core.web.controller.BaseController;
import com.ruoyi.project.domain.HeEventDto;
import com.ruoyi.project.module.ny.vo.ExportVo;
import com.ruoyi.project.service.IHeEventReportService;
import com.ruoyi.project.service.ISysConfigService;
import com.ruoyi.project.service.impl.HeEventReportServiceImpl;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.redis.core.HashOperations;
import org.springframework.data.redis.core.RedisTemplate;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.FileOutputStream;
import java.io.IOException;
import java.lang.reflect.Field;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.concurrent.TimeUnit;

@RestController
@RequestMapping("/ny/export")
public class NyExportBaseConroller extends BaseController {

    @Autowired
    private ISysConfigService iSysConfigService;

    private List result;

    private String tableName;

    @Autowired
    private IHeEventReportService iHeEventReportService;

    @Autowired
    private RedisTemplate redisTemplate;

    @PostMapping("/export")
    public String export(@RequestBody ExportVo exportVo, HttpServletRequest request, HttpServletResponse response) throws IOException {
        System.out.println(request);
        System.out.println(response);
        //startPage();

        this.tableName = exportVo.getDataTable();
        // 创建一个新的Excel工作簿
        Workbook workbook = WorkbookFactory.create(true);
        Sheet sheet = workbook.createSheet("Sheet1");

        //设置单元格样式
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.CENTER); // 水平居中对齐
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); // 垂直居中对齐

        // 设置边框样式
//        cellStyle.setBorderTop(BorderStyle.THIN);
//        cellStyle.setBorderBottom(BorderStyle.THIN);
//        cellStyle.setBorderLeft(BorderStyle.THIN);
//        cellStyle.setBorderRight(BorderStyle.THIN);

        List<String> params = exportVo.getParam();//获得需要导出的自定义参数

        if (params == null || params.size() == 0) {
            return "500";
        }
        // 创建表头
        Row headerRow = sheet.createRow(0);
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, params.size() - 1));//行行列列
        Cell headerCell = headerRow.createCell(0);

        headerCell.setCellValue("导出信息表");
        headerCell.setCellStyle(cellStyle);

        //根据自定义字段导出
        XSSFRow row1 = (XSSFRow) sheet.createRow((int) 1);
        for (int i = 0; i < params.size(); i++) {
//            row1.createCell(i).setCellStyle(cellStyle);
            Map<String, String> map = getColumnComments(exportVo.getDataTable());
            if(map.containsKey(params.get(i)))
            row1.createCell(i).setCellValue(map.get(params.get(i)));
        }

        // ---------------------------------------查询表数据-----------------------------------------//
        String tableName = exportVo.getDataTable();
        if (tableName != null && tableName.equals("he_event_dto")) {

            HeEventDto heEventDto = exportVo.getQueryParams();
            System.out.println(heEventDto);


            result = iHeEventReportService.selectHeEventDtoList(heEventDto);
            //System.out.println(result);
            RowNum(sheet, params);
        }

        // 创建文件名格式化对象,导出到桌面
        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyyMMdd_HHmmss");
        String userHome = System.getProperty("user.home");
        String filePath = userHome + "/Desktop/" + dateFormat.format(new Date()) + ".xlsx";
        try (FileOutputStream fileOut = new FileOutputStream(filePath)) {
            workbook.write(fileOut);
        }
        return "200";
    }

    private void RowNum(Sheet sheet, List<String> params) {
        int rowNum = 2; // 从第二行开始填充数据
        for (Object obj : result) {
            XSSFRow row = (XSSFRow) sheet.createRow(rowNum);
            for (int i = 0; i < params.size(); i++) {
                Object value = getValueByFieldName(obj, params.get(i)); // 根据字段名从对象中获取对应值

                /*if (getValueByLabelIfTypeExists(params.get(i), value.toString())!=null){
                    String redisValue = getValueByLabelIfTypeExists(params.get(i), value.toString());
                    row.createCell(i).setCellValue(redisValue != null ? redisValue : value.toString());
                }
                row.createCell(i).setCellValue(value != null ? value.toString() : ""); // 将值填充到单元格，如果值为null则填充空字符串*/

                //判断是否包含字典信息
                if(isTypeExpired("userSex")){
                    getDictionaryDataFromDB();
                }
                if (isTypeExistsInRedis(params.get(i))) {
                    if (value!=null && isFieldExistsInRedis(params.get(i), value.toString())) {
                        String redisValue = getValueByLabel(params.get(i), value.toString());
                        row.createCell(i).setCellValue(redisValue != null ? redisValue : value.toString());
                    }else{
                        row.createCell(i).setCellValue(""); // 将值填充到单元格，如果值为null则填充空字符串*/
                    }
                }else{
                    row.createCell(i).setCellValue(value != null ? value.toString() : ""); // 将值填充到单元格，如果值为null则填充空字符串*/
                }
            }
            rowNum++;
        }
    }

    // 根据字段名从对象中获取对应值，假设使用反射来实现
    private Object getValueByFieldName(Object obj, String fieldName) {
        try {
            Field field = obj.getClass().getDeclaredField(fieldName);
            field.setAccessible(true);
            return field.get(obj);
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }

    // 先判断 type 是否存在，再根据 label 获取对应的 value
    public String getValueByLabelIfTypeExists(String type, String label) {
        HashOperations<String, String, String> hashOperations = redisTemplate.opsForHash();

        if (hashOperations.hasKey(type, label)) {
            return hashOperations.get(type, label);
        } else {
            return null;
        }
    }

    public void getDictionaryDataFromDB() {
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;

        try {
            // 连接数据库
            String url = iSysConfigService.selectConfigByKey("sys.project.mysql.url");
            String user = iSysConfigService.selectConfigByKey("sys.project.mysql.user");
            String password = iSysConfigService.selectConfigByKey("sys.project.mysql.password");

            conn = DriverManager.getConnection(url, user, password);

            // SQL 查询语句
            String sql = "SELECT dict_type, dict_label, dict_value FROM sys_dict_data";

            // 执行查询
            stmt = conn.prepareStatement(sql);
            rs = stmt.executeQuery();

            HashOperations<String, String, String> hashOperations = redisTemplate.opsForHash();

            // 遍历查询结果，逐个存储到 Redis 中
            while (rs.next()) {
                String type = reshape(rs.getString("dict_type"));
                String value = rs.getString("dict_label");
                String label = rs.getString("dict_value");

                // 判断键是否存在
                if (!redisTemplate.hasKey(type)) {
                    // 如果键不存在，则创建一个新的 Hash 类型键
                    hashOperations.putAll(type, new HashMap<>());
                    // 设置过期时间
                    redisTemplate.expire(type, 100, TimeUnit.SECONDS);
                }

                // 将字典信息存储到 Redis Hash 中
                hashOperations.put(type, label, value);

               /* System.out.println(redisTemplate.type(type));
                System.out.println("dict_type："+rs.getString("dict_type"));
                System.out.println("dict_label："+rs.getString("dict_label"));
                System.out.println("dict_value："+rs.getString("dict_value"));
                System.out.println("####################################################");*/
                // 将字典信息存储到 Redis Hash 中
                hashOperations.put(type, label, value);

                // 设置过期时间
                redisTemplate.expire(type, 100, TimeUnit.SECONDS);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // 关闭连接
            try {
                if (rs != null) {
                    rs.close();
                }
                if (stmt != null) {
                    stmt.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
    // 判断 type 是否存在于 Redis 中的方法
    private boolean isTypeExistsInRedis(String type) {
        return redisTemplate.hasKey(type);
    }

    // 判断 label 是否存在于 Redis 中的方法
    private boolean isFieldExistsInRedis(String type, String label) {
        HashOperations<String, String, String> hashOperations = redisTemplate.opsForHash();
        return hashOperations.hasKey(type, label);
    }
    // 根据 label 获取值的方法
    private String getValueByLabel(String type, String label) {
        HashOperations<String, String, String> hashOperations = redisTemplate.opsForHash();
        return hashOperations.get(type, label);
    }
    private boolean isTypeExpired(String type) {
        // 获取 Redis 中键的 TTL
        Long ttl = redisTemplate.getExpire(type, TimeUnit.SECONDS);
        // 如果 TTL 大于 0，则表示未过期；如果 TTL 等于 0 或者小于 0，则表示已经过期
        return ttl != null && ttl <= 0;
    }

    //统一前缀格式，去掉_
    public String reshape(String str) {
        String input = str;

        // 找到第一个下划线的索引
        int indexOfUnderscore = input.indexOf("_");
        if (indexOfUnderscore != -1) {
            // 截取从第一个下划线之后开始的子字符串
            input = input.substring(indexOfUnderscore + 1);
        } else {
            // 如果字符串中没有下划线，则不做任何处理
            System.out.println("No underscore found in the input string.");
        }
        return underscoreToCamelCase(input);
    }
    // 将字体转换为驼峰形
    public String underscoreToCamelCase(String underscoreString) {
        StringBuilder camelCaseString = new StringBuilder();
        boolean capitalizeNext = false;

        for (char c : underscoreString.toCharArray()) {
            if (c == '_') {
                capitalizeNext = true;
            } else {
                if (capitalizeNext) {
                    camelCaseString.append(Character.toUpperCase(c));
                    capitalizeNext = false;
                } else {
                    camelCaseString.append(c);
                }
            }
        }
        return camelCaseString.toString();
    }

    public Map<String, String> getColumnComments(String tableName) {
        Map<String, String> map = new HashMap<>();

        String url = iSysConfigService.selectConfigByKey("sys.project.mysql.url");
        String user = iSysConfigService.selectConfigByKey("sys.project.mysql.user");
        String password = iSysConfigService.selectConfigByKey("sys.project.mysql.password");

        try (Connection conn = DriverManager.getConnection(url, user, password)) {
            String sql = "SELECT column_name AS columnName, column_comment AS columnComment FROM information_schema.columns WHERE table_name = '" + tableName + "'";
            try (Statement stmt = conn.createStatement();
                 ResultSet rs = stmt.executeQuery(sql)) {
                while (rs.next()) {
                    String name = rs.getString("columnName");
                    String comment = rs.getString("columnComment");
                    map.put(underscoreToCamelCase(name), comment);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return map;
    }
}


